import sys
import os
import logging
import re
import json
from typing import Dict, List, Any, Optional, Tuple
from decimal import Decimal

# 添加项目根路径到 sys.path
sys.path.append(os.path.join(os.path.dirname(os.path.abspath(__file__)), '..', '..'))

# 导入 MySQLUtil
from shared.utils.MySQLUtil import MySQLUtil

# 设置日志
logger = logging.getLogger(__name__)

def get_各学历_各性别下毕业生人数及男女性别比(
    project_id: int,
    questionnaire_ids: List[int],
    product_code: Optional[str] = None,
    project_code: Optional[str] = None,
    region_code: Optional[str] = None
) -> Dict[str, Any]:
    """
    各学历，各性别下毕业生人数及男女性别比 - 指标计算函数
    
    ## 指标说明
    该指标用于统计不同学历、不同性别下的毕业生人数及男女性别比例。
    业务意义：分析不同学历层次中男女毕业生的分布情况，为教育决策提供数据支持。
    计算方式：按照学历和性别分组统计人数，计算各学历下性别比例和占比。
    
    ## Args
        project_id (int): 项目ID，用于查询项目配置信息
        questionnaire_ids (List[int]): 问卷ID集合，用于确定数据范围
        product_code (Optional[str]): 产品编码，用于路由到特定计算逻辑
        project_code (Optional[str]): 项目编码，用于路由到特定计算逻辑
        region_code (Optional[str]): 区域编码，用于路由到特定计算逻辑
        
    ## 示例
    ### 输入
    ```json
    {
        "project_id": 5895,
        "questionnaire_ids": [11158, 11159]
    }
    ```
    
    ### 输出
    ```json
    {
        "success": true,
        "message": "ok", 
        "code": 0,
        "result": [
            {
                "education": "本科",
                "male_count": 120,
                "female_count": 80,
                "total": 200,
                "male_ratio": 0.6,
                "female_ratio": 0.4,
                "gender_ratio": 1.5
            },
            {
                "education": "硕士",
                "male_count": 90,
                "female_count": 60,
                "total": 150,
                "male_ratio": 0.6,
                "female_ratio": 0.4,
                "gender_ratio": 1.5
            },
            {
                "education": "汇总",
                "male_count": 210,
                "female_count": 140,
                "total": 350,
                "male_ratio": 0.6,
                "female_ratio": 0.4,
                "gender_ratio": 1.5
            }
        ]
    }
    ```
    """
    logger.info(f"开始计算指标: 各学历，各性别下毕业生人数及男女性别比, 项目ID: {project_id}")
    
    try:
        db = MySQLUtil()  

        # 1. 查询项目配置信息
        project_sql = """
        SELECT client_code, item_year, split_tb_paper 
        FROM client_item 
        WHERE id = %s
        """
        project_info = db.fetchone(project_sql, (project_id,))
        if not project_info:
            raise ValueError(f"未找到项目ID={project_id}的配置信息")

        client_code = project_info['client_code']
        item_year = project_info['item_year']
        shard_tb_key = re.sub(r'^[A-Za-z]*0*', '', client_code)
        
        logger.info(f"项目配置: client_code={client_code}, item_year={item_year}, shard_tb_key={shard_tb_key}")

        # 2. 查询毕业生数据
        student_table = f"dim_client_target_baseinfo_student_calc_{item_year}"
        query_sql = f"""
        SELECT
            education,
            sex,
            COUNT(*) as count
        FROM
            {student_table}
        WHERE
            shard_tb_key = %s
        GROUP BY education, sex
        """
        results = db.fetchall(query_sql, (shard_tb_key,))
        if not results:
            raise ValueError("未查询到毕业生数据")
        
        logger.info(f"查询到毕业生数据: {len(results)}条记录")

        # 3. 数据处理
        education_data = {}
        total_male = 0
        total_female = 0
        
        for row in results:
            education = row['education']
            sex = row['sex']
            count = int(row['count'])
            
            if education not in education_data:
                education_data[education] = {
                    'male_count': 0,
                    'female_count': 0,
                    'total': 0
                }
            
            if sex == '男':
                education_data[education]['male_count'] += count
                total_male += count
            elif sex == '女':
                education_data[education]['female_count'] += count
                total_female += count
            
            education_data[education]['total'] += count
        
        # 4. 计算比例和性别比
        final_result = []
        for education, data in education_data.items():
            male_count = data['male_count']
            female_count = data['female_count']
            total = data['total']
            
            male_ratio = male_count / total if total > 0 else 0
            female_ratio = female_count / total if total > 0 else 0
            gender_ratio = male_count / female_count if female_count > 0 else 0
            
            final_result.append({
                "education": education,
                "male_count": male_count,
                "female_count": female_count,
                "total": total,
                "male_ratio": round(float(male_ratio), 4),
                "female_ratio": round(float(female_ratio), 4),
                "gender_ratio": round(float(gender_ratio), 2)
            })
        
        # 5. 添加汇总数据
        total = total_male + total_female
        total_male_ratio = total_male / total if total > 0 else 0
        total_female_ratio = total_female / total if total > 0 else 0
        total_gender_ratio = total_male / total_female if total_female > 0 else 0
        
        final_result.append({
            "education": "汇总",
            "male_count": total_male,
            "female_count": total_female,
            "total": total,
            "male_ratio": round(float(total_male_ratio), 4),
            "female_ratio": round(float(total_female_ratio), 4),
            "gender_ratio": round(float(total_gender_ratio), 2)
        })

        logger.info(f"指标 '各学历，各性别下毕业生人数及男女性别比' 计算成功")
        return {
            "success": True,
            "message": "ok",
            "code": 0,
            "result": final_result
        }

    except Exception as e:
        logger.error(f"计算指标 '各学历，各性别下毕业生人数及男女性别比' 时发生错误: {str(e)}", exc_info=True)
        return {
            "success": False,
            "message": f"数据获取失败: 各学历，各性别下毕业生人数及男女性别比",
            "code": 500,
            "error": str(e)
        }